﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DacSanMienTayOBJ
{
    public class Sqlhelper
    {
        private ConnectionType _connectionType;
        private SqlConnection _mysqlConnection = new SqlConnection();
        private SqlCommand _mysqlCommand = new SqlCommand();


        public Sqlhelper(ConnectionType connectionType)
        {
            this._connectionType = connectionType;
            _mysqlConnection = new SqlConnection(getConnectionString());
        }

        private String getConnectionString()
        {
            if (_connectionType == ConnectionType.UserConnection)
            {
                if (_mysqlConnection != null)
                {
                    if (_mysqlConnection.State != ConnectionState.Open)
                    {
                        WebConfig.loadWebConfig();
                    }
                }
                else
                {
                    WebConfig.loadWebConfig();
                }
                return WebConfig.USER_CONNECT_STRING;
            }

            else if (_connectionType == ConnectionType.AdminConnection)
            {
                if (_mysqlConnection != null)
                {
                    if (_mysqlConnection.State != ConnectionState.Open)
                    {
                        WebConfig.loadWebConfig();
                    }
                }
                else
                {
                    WebConfig.loadWebConfig();
                }
                return WebConfig.ADMIN_CONNECT_STRING;
            }
            
            else
            {
                return null;
            }
        }

        public SqlCommand GetCommand(string sql, SqlParameter []para,bool haveStore)
        {
             try
            {
                _mysqlCommand = new SqlCommand (sql, _mysqlConnection);
                if (haveStore)
                {
                    _mysqlCommand.CommandType = CommandType.StoredProcedure;
                }
                addParameter(para);
                _mysqlConnection.Open();

                return _mysqlCommand;
            }
            catch
            {
                return null;
            }
        }

        public SqlCommand GetCommandNonParameter(string sql, bool haveStore)
        {
            try
            {
                _mysqlCommand = new SqlCommand(sql, _mysqlConnection);
                if (haveStore)
                {
                    _mysqlCommand.CommandType = CommandType.StoredProcedure;
                }
               
                _mysqlConnection.Open();

                return _mysqlCommand;
            }
            catch
            {
                return null;
            }
        }

          private void addParameter(params SqlParameter[] parameter)
        {
            if (_mysqlCommand != null)
            {
                foreach (SqlParameter param in parameter)
                {
                    _mysqlCommand.Parameters.Add(param);
                }
            }
        }

        public void destroy()
        {
            if (_mysqlConnection != null)
            {
                if (_mysqlConnection.State == ConnectionState.Open)
                {
                    _mysqlConnection.Close();
                    _mysqlConnection.Dispose();
                }
            }
          
            if (_mysqlCommand != null)
                _mysqlCommand.Dispose();
           
            _mysqlConnection = null;
            _mysqlCommand = null;
          
        }
    }
}
